﻿using System.Data;
using System.Text;
using Microsoft.ApplicationBlocks.Data;

namespace DataObject.Tag
{
    internal class TagDAL: DAL
    {
        /***************************************
        = Back End
        -------------------------------------- */
        internal static int Tag_Create(TagObject o)
        {
            return (int)SqlHelper.ExecuteScalar(Conn, "Tag_Create", o.Name, o.CreatedBy);
        }

        internal static void Tag_Update(TagObject o)
        {
            SqlHelper.ExecuteNonQuery(Conn, "Tag_Update", o.TagId, o.Name, o.EditedBy);
        }

        internal static void Tag_Delete(int id)
        {
            SqlHelper.ExecuteNonQuery(Conn, "Tag_Delete", id);
        }

        internal static int Tag_CheckNameExist(int id, string name)
        {
            sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT({0}) ", Constants.Table_Tag.TagId);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_Tag.TableName);
            sb.AppendFormat("WHERE {0} = '{2}' AND {1} != {3} ", Constants.Table_Tag.Name, Constants.Table_Tag.TagId, name, id);
            return (int)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader Tag_GetAll()
        {
            sb = new StringBuilder();
            sb.AppendLine("SELECT * ");
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_Tag.TableName);
            sb.AppendFormat("ORDER BY [{0}] ASC", Constants.Table_Tag.Name);
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader Tag_GetById(int id)
        {
            sb = new StringBuilder();
            sb.AppendLine("SELECT TOP(1) * ");
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_Tag.TableName);
            sb.AppendFormat("WHERE {0} = {1}", Constants.Table_Tag.TagId, id);
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader Tag_GetById_TypeId(long id, int typeId)
        {
            sb = new StringBuilder();
            sb.AppendLine("SELECT t.* ");
            sb.AppendFormat("FROM [dbo].[{0}]  AS t ", Constants.Table_Tag.TableName);
            sb.AppendFormat("INNER JOIN [dbo].[{0}] AS td ", Constants.Table_TagDetails.TableName);
            sb.AppendFormat("ON td.{1} = t.{0} ", Constants.Table_Tag.TagId,  Constants.Table_TagDetails.TagId);
            sb.AppendFormat("WHERE td.{0} = {2} AND td.{1} = {3}", Constants.Table_TagDetails.Id, Constants.Table_TagDetails.TypeId, id, typeId);
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        internal static int Tag_GetCount()
        {
            sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT({0}) ", Constants.Table_Tag.TagId);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_Tag.TableName);
            return (int)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader Tag_GetListPaging(int pageIndex, int pageSize)
        {
            sb = new StringBuilder();
            sb.AppendLine("DECLARE @StartRow INT");
            sb.AppendFormat("SET @StartRow = ({0} - 1) * {1} + 1 ", pageIndex, pageSize);
            sb.AppendLine("DECLARE @EndRow INT");
            sb.AppendFormat("SET @EndRow = {0} * {1}; ", pageIndex, pageSize);
            sb.AppendLine("WITH temp AS");
            sb.AppendLine("(");
            sb.AppendLine("SELECT TOP(@EndRow) * ");
            sb.AppendFormat(", ROW_NUMBER() OVER (ORDER BY [{0}] DESC) AS 'RowNumber' ", Constants.Table_Tag.EditedOn);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_Tag.TableName);
            sb.AppendLine(")");
            sb.AppendLine("SELECT * FROM temp ");
            sb.AppendLine("WHERE RowNumber BETWEEN @StartRow AND @EndRow");
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }
    }
}
